#!/usr/bin/python
# -*- coding: utf-8 -*-

"""     This program is free software; you can redistribute it and/or modify
#       it under the terms of the GNU General Public License as published by
#       the Free Software Foundation; either version 2 of the License, or
#       (at your option) any later version.
#
#       This program is distributed in the hope that it will be useful,
#       but WITHOUT ANY WARRANTY; without even the implied warranty of
#       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#       GNU General Public License for more details.
#
#       You should have received a copy of the GNU General Public License
#       along with this program; if not, write to the Free Software
#       Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
#       MA 02110-1301, USA.

"""

__author__ = "Evgeniy Frolov"
__email__ = "profisphantom@gmail.com"
__version__ = "2.0.1"

import os
import tempfile
import datetime
import json

from sqlalchemy.engine import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.sql.expression import distinct
from sqlalchemy.schema import DDL

from xlwt.Workbook import Workbook
from xlwt.Style import XFStyle
import xlrd

from rdb.old_app.new_config.database import DATABASE_URL
from rdb.old_app.new_config.files import TMPDIR

from rdb.old_app.core.orm import (Model, Customer, Destination, CoolerReceipt,
                                  RepairNote, SpareParts, CustomerGroup)
from rdb.old_app.core.json_answers import JSONSuccess

from rdb.old_app.query_class import query_getPrintDetailedInvoicebyPeriod, \
    query_getPrintOtherSparePartsperRepairNote


class Storage(object):

    def __init__(self):
        self.__engine = create_engine(DATABASE_URL, echo=False)
        self.Session = scoped_session(sessionmaker(bind=self.__engine))


class RepairNoteStorage(Storage):

    def prepare_file(self, file_name):
        session = self.Session()
        try:
            book = xlrd.open_workbook(os.path.join(TMPDIR, file_name))
            sheet = book.sheet_by_index(0)
            x = u""
            js_table = []
            for row in xrange(sheet.nrows-1):

                customer = str(sheet.cell_value(row+1, 0))
                model = str(sheet.cell_value(row+1, 1))
                if sheet.cell_type(row+1, 2) == xlrd.XL_CELL_NUMBER:
                    sn = str(int(sheet.cell_value(row+1, 2)))
                else:
                    sn = str(sheet.cell_value(row+1, 2))
                if sheet.cell_type(row+1, 3) == xlrd.XL_CELL_NUMBER:
                    inv = str(int(sheet.cell_value(row+1, 3)))
                else:
                    inv = str(sheet.cell_value(row+1, 3))
                ddate = str(sheet.cell_value(row+1, 4))
                dnote = str(sheet.cell_value(row+1, 5))

                js_row = {
                    u"customer": customer,
                    u"model": model,
                    u"sn": sn,
                    u"inv": inv,
                    u"ddate": ddate,
                    u"dnote": dnote,
                    u"error": 0,
                    u"error_message": u"",
                    u"original_exc": u"",
                    u"rnid" : 0
                }
                try:
                    customer_model = session.query(Customer)\
                      .filter(Customer.name == customer)\
                      .one()
                except Exception as e:
                    js_row[u'error'] = 1
                    js_row[u'error_message'] = (u'Customer %s not found' %
                                                customer)
                    js_row[u'original_exc'] = str(e)
                try:
                    model_model = session.query(Model)\
                      .filter(Model.name == model)\
                      .one()
                except Exception as e:
                    if not js_row[u'error']:
                        js_row[u'error'] = 2
                        js_row[u'error_message'] = u'Model %s not found' % model
                        js_row[u'original_exc'] = str(e)

                try:
                    cooler_list_q = session.query(CoolerReceipt)\
                      .filter(CoolerReceipt.customer == customer_model.id)\
                      .filter(CoolerReceipt.model == model_model.id)\
                      .filter(CoolerReceipt.snumber == sn)
                    if inv:
                        cooler_list_q.filter(CoolerReceipt.inumber == inv)
                    cooler_list = cooler_list_q.all()

                    if (not cooler_list) and (not js_row[u'error']):
                        js_row[u'error'] = 3
                        js_row[u'error_message'] = (
                            u"Refrigerator not found (serial number: %s, "
                            "inventory number: %s)" % (sn, inv))
                    else:
                        try:
                            rn_model = session.query(RepairNote)\
                              .filter(RepairNote.cooler.in_(
                                  [c.id for c in cooler_list]))\
                              .filter(RepairNote.dispatchnote == None)\
                              .one()
                            js_row[u'rnid'] = rn_model.id
                        except Exception as e:
                            if not js_row[u'error']:
                                js_row[u'error'] = 4
                                js_row[u'error_message'] = (
                                    u'Repair Note not found')
                                js_row[u'original_exc'] = str(e)
                except Exception as e:
                    js_row[u'error'] = 99
                    js_row[u'error_message'] = u"Unknown error"
                    js_row[u'original_exc'] = str(e)
                if not dnote:
                    js_row[u'error'] = 5
                    js_row[u'error_message'] = u"Field Dispatch Note required!"
                js_table.append(js_row)
            new_file_name = file_name.split('.')[0] + '.json'
            f = open(os.path.join(TMPDIR, new_file_name), 'w')
            f.write(json.dumps(js_table))
            f.close()
            return new_file_name
        #session.query(RepairNote).all()[0]
        finally:
            session.close()

    def get_prepare_json(self, file_name):
        with open(os.path.join(TMPDIR, file_name)) as f:
            return json.loads(f.read())

    def commit_import(self, file_name):
        json_table = self.get_prepare_json(file_name)
        session = self.Session()
        for jrow in json_table:
            if jrow["error"] == 0:
                rn_model = session.query(RepairNote)\
                  .filter(RepairNote.id == jrow["rnid"])\
                  .one()
                rn_model.dispatchnote = jrow["dnote"]
                if jrow["ddate"]:
                    rn_model.dispatchdate = jrow["ddate"]
        session.commit()

    def get_dispatch_note_list(self, offset, limit, query):
        session = self.Session()
        try:
            dsn_list = session.query(distinct(RepairNote.dispatchnote))\
                              .filter(RepairNote.dispatchnote.like('%' + query + '%'))\
                              .offset(offset)\
                              .limit(limit)\
                              .all()
            count = session.query(distinct(RepairNote.dispatchnote))\
                              .filter(RepairNote.dispatchnote.like('%' + query + '%'))\
                              .count()
            result = []
            for dispatchnote in dsn_list:
                result.append({
                    'id': dispatchnote[0],
                    'descr': dispatchnote[0]
                })
            return JSONSuccess(dsn_list=result, count=count)
        finally:
            session.close()

    def get_serial_number_list(self, offset, limit, query):
        session = self.Session()
        try:
            sn_list = session.query(distinct(CoolerReceipt.snumber))\
                             .filter(CoolerReceipt.snumber.like('%' + query + '%'))\
                             .offset(offset)\
                             .limit(limit)\
                             .all()
            count = session.query(distinct(CoolerReceipt.snumber))\
                           .filter(CoolerReceipt.snumber.like('%' + query + '%'))\
                           .count()
            result = []
            for sn in sn_list:
                result.append({
                    'id': sn[0],
                    'descr': sn[0]
                })
            return JSONSuccess(sn_list=result, count=count)
        finally:
            session.close()

    def get_customer_list(self, offset, limit, query):
        session = self.Session()
        try:
            customer_list = session.query(Customer)\
                                   .filter(Customer.name.like('%' + query + '%'))\
                                   .offset(offset)\
                                   .limit(limit)\
                                   .all()
            count = session.query(Customer)\
                           .filter(Customer.name.like('%' + query + '%'))\
                           .count()
            result = []
            for customer in customer_list:
                result.append({
                    'id': customer.id,
                    'name': customer.name,
                    'group': customer.group
                })
            return JSONSuccess(customer_list=result, count=count)
        finally:
            session.close()

    def get_customers_list(self, offset, limit):
        session = self.Session()
        try:
            result = []
            for customer in session.query(Customer).all():
                result.append({
                    'id': customer.id,
                    'name': customer.name,
                    'group': customer.group_obj.group
                })
            return JSONSuccess(customers_list=result, count=len(result))
        finally:
            session.close()

    def update_customer(self, id, name, group):
        session = self.Session()
        try:
            customer = session.query(Customer).filter(Customer.id == id).one()
            group_model = session.query(CustomerGroup).filter(
                CustomerGroup.group == group).one()
            customer.name = name
            customer.group_obj = group_model
            session.commit()
            return JSONSuccess(customer_list={'id': id,
                                              'name': name,
                                              'group': group})
        finally:
            session.close()

    def delete_customer(self, id):
        session = self.Session()
        try:
            session.query(Customer).filter(Customer.id == id).delete()
            session.commit()
        finally:
            session.close()

    def create_customer(self, name, group):
        session = self.Session()
        try:
            if group == '':
                group_model = session.query(CustomerGroup).first()
            else:
                group_model = session.query(
                    CustomerGroup).filter(CustomerGroup.group == group).one()
            customer = Customer(descr=name, group_id=group_model.id)
            session.add(customer)
            session.commit()
            return JSONSuccess(customer_list={'id': customer.id,
                                              'name': customer.name,
                                              'group': customer.group_id})
        finally:
            session.close()

    def get_customer_group_list(self):
        session = self.Session()
        try:
            result = []
            for group in session.query(CustomerGroup).all():
                result.append({
                    'id': group.id,
                    'group': group.group
                })
            return JSONSuccess(group_list=result, count=len(result))
        finally:
            session.close()

    def get_destination_list(self, offset, limit, query):
        session = self.Session()
        try:
            destination_list = session.query(Destination)\
                                   .filter(Destination.daddr.like('%' + query + '%'))\
                                   .offset(offset)\
                                   .limit(limit)\
                                   .all()
            count = session.query(Destination)\
                           .filter(Destination.daddr.like('%' + query + '%'))\
                           .count()
            result = []
            for destination in destination_list:
                result.append({
                    'id': destination.id,
                    'descr': destination.daddr,
                })
            return JSONSuccess(destination_list=result, count=count)
        finally:
            session.close()

    def get_model_list(self, offset, limit, query):
        session = self.Session()
        try:
            model_list = session.query(Model)\
                                .filter(Model.name.like('%' + query + '%'))\
                                .offset(offset)\
                                .limit(limit)\
                                .all()
            count = session.query(Model)\
                           .filter(Model.name.like('%' + query + '%'))\
                           .count()
            result = []
            for model in model_list:
                result.append({
                    'id': model.id,
                    'name': model.name
                })
            return JSONSuccess(model_list=result, count=count)
        finally:
            session.close()

    def get_report(self, offset, limit, from_date, to_date, dispatchnote=None, \
            serial_number=None, customer=None, model=None, destination=None):
        session = self.Session()
        try:
            other_param = ""
            if dispatchnote:
                other_param += (" AND `t1`.`dispatchnote` = '%s'" % dispatchnote)
            if serial_number:
                other_param += (" AND `t2`.`snumber` = '%s'" % serial_number)
            if customer:
                other_param += (" AND `t2`.`customer` = %s" % customer)
            if model:
                other_param += (" AND `t2`.`model` = %s" % model)
            if destination:
                other_param += (" AND `t2`.`destination` = %s" % destination)

            query_str = query_getPrintDetailedInvoicebyPeriod % (from_date, to_date, other_param)
            report_list = session.execute(DDL(query_str))

            result = []
            rn_code = ""
            for row in report_list:
                result.append({
                        'repairnumber': row[0],
                        'dispatchdate': row[1].strftime("%Y-%m-%d"),
                        'destination': row[2],
                        'serial_number': row[3],
                        'inventory_number': row[4],
                        'model': row[5],
                        'brand': row[6],
                        'rt_description': row[7],
                        'rt_price': "%.2f" % float(row[8] if row[8] else 0),
                        'rt_parts_price': "%.2f" % float(row[9] if row[9] else 0),
                        'rt_other_part_price': ("%.2f" % float(row[10] if \
                            row[10] else 0)) if rn_code != row[0] else ""
                    })
                rn_code = row[0]
            return JSONSuccess(result=result, count=len(result))
#            report_list = session.query(RepairNote)\
#                                 .filter(RepairNote.dispatchdate >= from_date)\
#                                 .filter(RepairNote.dispatchdate <= to_date)\
#                                 .all()
#            result = []
#            for row in report_list:
#                for rt in row.repair_types:
#                    other_part_price = 0
#                    for sp in row.other_spare_parts:
#                        quantity = session.query(SPCodeTable)\
#                                          .filter(SPCodeTable.guid == row.otherspareparts)\
#                                          .filter(SPCodeTable.spcode == sp.id)\
#                                          .all()
#                        other_part_price = quantity[0].quantity
#                    result.append({
#                        'repairnumber': row.repairnumber,
#                        'serial_number': row.cooler_receipt.snumber if \
#                            row.cooler_receipt else '',
#                        'inventory_number': row.cooler_receipt.inumber if \
#                            row.cooler_receipt else '',
#                        'destination': row.cooler_receipt.destination_obj.daddr if\
#                            row.cooler_receipt else '',
#                        'model': row.cooler_receipt.model_obj.name if\
#                            row.cooler_receipt else '',
#                        'brand': row.brand,
#                        'rt_description': rt.repairTypeRus,
#                        'rt_price': rt.RepairTypePrice,
#                        'rt_parts_price': rt.SparePartsPrice,
#                        'rt_other_part_price': other_part_price
#                    })
#            return result
        finally:
            session.close()

    def get_customers_xls(self):
        workbook = Workbook()
        sheet = workbook.add_sheet(u'Данные')

        style_string = XFStyle()
        style_string.num_format_str = "@"

        sheet.write(0, 0, u"id", style_string)
        sheet.write(0, 1, u"Customer name", style_string)
        sheet.write(0, 2, u"Customer group", style_string)

        index = 1

        for item in self.get_customers_list(
                offset=0, limit=999).customers_list:

            sheet.write(index, 0, item['id'], style_string)
            sheet.write(index, 1, item['name'], style_string)
            sheet.write(index, 2, item['group'], style_string)

            index += 1

        unicname = os.path.basename(tempfile.mkstemp(prefix='Customers')[1])
        unicname += '.xls'
        workbook.save(os.path.join(TMPDIR, unicname))
        return "/tmp/" + unicname

    def get_xls(self, from_date, to_date, dispatchnote=None,
                serial_number=None, customer=None, model=None,
                destination=None):
        reverse = lambda x, y, z: (int(x), int(y), int(z))
        r = self.get_report(0, 65536, from_date, to_date, dispatchnote,
                            serial_number, customer, model, destination)
        workbook = Workbook()
        sheet = workbook.add_sheet(u'Данные')

        style_string = XFStyle()
        style_float = XFStyle()
        style_date = XFStyle()

        style_string.num_format_str = "@"
        style_float.num_format_str = '0.00'
        style_date.num_format_str = 'DD-MM-YY'

        sheet.write(0, 0, u"Номер ремонта", style_string)
        sheet.write(0, 1, u"Серийный номер", style_string)
        sheet.write(0, 2, u"Инвентарный номер", style_string)
        sheet.write(0, 3, u"Направление", style_string)
        sheet.write(0, 4, u"Модель", style_string)
        sheet.write(0, 5, u"Дата отгрузки", style_string)
        sheet.write(0, 6, u"Бренд", style_string)
        sheet.write(0, 7, u"Описание модуля ремонта", style_string)
        sheet.write(0, 8, u"Цена модуля", style_string)
        sheet.write(0, 9, u"Цена зап. частей модуля", style_string)
        sheet.write(0, 10, u"Цена других зап. частей", style_string)

        index = 0
        for row in r.result:
            index += 1

            sheet.write(index, 0, row['repairnumber'], style_string)
            sheet.write(index, 1, row['serial_number'], style_string)
            sheet.write(index, 2, row['inventory_number'], style_string)
            sheet.write(index, 3, row['destination'], style_string)
            sheet.write(index, 4, row['model'], style_string)
            sheet.write(index, 5, datetime.datetime(*reverse(
                *row['dispatchdate'].split("-"))), style_date)
            sheet.write(index, 6, row['brand'], style_string)
            sheet.write(index, 7, row['rt_description'], style_string)
            sheet.write(index, 8, float(row['rt_price']) if row['rt_price'] \
                else 0.0, style_float)
            sheet.write(index, 9, float(row['rt_parts_price']) if \
                row['rt_parts_price'] else 0.0, style_float)
            sheet.write(index, 10, float(row['rt_other_part_price']) if \
                row['rt_other_part_price'] else "", style_float)

        unicname = os.path.basename(tempfile.mkstemp(prefix='ReportDetailedInvoiceByPeriod')[1])
        unicname += '.xls'
        workbook.save(os.path.join(TMPDIR, unicname))
        return "/tmp/" + unicname

    def get_report2(self, offset, limit, from_date, to_date, dispatchnote=None, \
            serial_number=None, customer=None, model=None, destination=None):
        session = self.Session()
        try:
            other_param = ""
            if dispatchnote:
                other_param += (" AND `q1`.`dispatchnote` = '%s'" % dispatchnote)
            if serial_number:
                other_param += (" AND `q1`.`snumber` = '%s'" % serial_number)
            if customer:
                other_param += (" AND `q1`.`customer` = %s" % customer)
            if model:
                other_param += (" AND `q1`.`model` = '%s'" % model)
            if destination:
                other_param += (" AND `q1`.`destination` = %s" % destination)

            query_str = query_getPrintOtherSparePartsperRepairNote
            query_str = query_getPrintOtherSparePartsperRepairNote % \
                "`q1`.`dispatchdate` >= '%s' AND `q1`.`dispatchdate` <= '%s' %s"
            query_str = query_str % (from_date, to_date, other_param)
            report_list = session.execute(DDL(query_str))

            result = []
            for row in report_list:
                result.append({
                        'repairnumber': row[3],
                        'repairdate': row[8].strftime("%Y-%m-%d"),
                        'dispatchdate': row[2].strftime("%Y-%m-%d"),
                        'destination': row[5],
                        'serial_number': row[4],
                        'inventory_number': row[6],
                        'model': row[7],
                        'sp_code': row[13],
                        'sp_description': row[14],
                        'quantity': row[15],
                        'price': "%.2f" % float(row[16] if row[16] else 0)
                    })
            return JSONSuccess(result=result, count=len(result))
        finally:
            session.close()

    def get_xls2(self, from_date, to_date, dispatchnote=None, serial_number=None, \
        customer=None, model=None, destination=None):
        reverse = lambda x, y, z: (int(x), int(y), int(z))
        r = self.get_report2(0, 65536, from_date, to_date, dispatchnote, \
            serial_number, customer, model, destination)
        workbook = Workbook()
        sheet = workbook.add_sheet(u'Данные')

        style_string = XFStyle()
        style_float = XFStyle()
        style_date = XFStyle()
        style_int = XFStyle()

        style_string.num_format_str = "@"
        style_float.num_format_str = '0.00'
        style_date.num_format_str = 'DD-MM-YY'
        style_int.num_format_str = '0'

        sheet.write(0, 0, u"Модель", style_string)
        sheet.write(0, 1, u"Серийный номер", style_string)
        sheet.write(0, 2, u"Инвентарный номер", style_string)
        sheet.write(0, 3, u"Направление", style_string)
        sheet.write(0, 4, u"Номер ремонта", style_string)
        sheet.write(0, 5, u"Дата ремонта", style_string)
        sheet.write(0, 6, u"Дата отгрузки", style_string)
        sheet.write(0, 7, u"Код зап. части", style_string)
        sheet.write(0, 8, u"Описание зап. части", style_string)
        sheet.write(0, 9, u"кол-во", style_string)
        sheet.write(0, 10, u"Цена", style_string)

        index = 0
        for row in r.result:
            index += 1

            sheet.write(index, 0, row['model'], style_string)
            sheet.write(index, 1, row['serial_number'], style_string)
            sheet.write(index, 2, row['inventory_number'], style_string)
            sheet.write(index, 3, row['destination'], style_string)
            sheet.write(index, 4, row['repairnumber'], style_string)
            sheet.write(index, 5, datetime.datetime(*reverse(*row['repairdate'].split("-"))), style_date)
            sheet.write(index, 6, datetime.datetime(*reverse(*row['dispatchdate'].split("-"))), style_date)
            if row['sp_code']:
                sheet.write(index, 7, int(row['sp_code']), style_int)
            sheet.write(index, 8, row['sp_description'], style_string)
            sheet.write(index, 9, row['quantity'], style_int)
            sheet.write(index, 10, float(row['price']) if row['price'] \
                else 0.0, style_float)

        unicname = os.path.basename(tempfile.mkstemp(prefix='ReportSparePerRepairNote')[1])
        unicname += '.xls'
        workbook.save(os.path.join(TMPDIR, unicname))
        return "/tmp/" + unicname


class SparePartsStorage(Storage):

    def get_spare_parts_list(self):
        session = self.Session()
        try:
            splist = session.query(SpareParts)\
                            .all()
            result = []
            for row in splist:
                models = []
                for model in row.models:
                    models.append(model.descr)
                result.append({
                    'id': row.id,
                    'code': row.code,
                    'description': row.description,
                    'description_rus': row.description_rus,
                    'unit': row.unit_descriptor.descr,
                    'integer': row.integer,
                    'safety_stock': row.safety_stock,
                    'planning_type': row.planning_type,
                    'planning_condition': row.planning_condition,
                    'models': ', '.join(models)
                })
            return JSONSuccess(spare_parts_list=result, count=len(result))
        finally:
            session.close()

    def remove_item(self, id_item):
        session = self.Session()
        try:
            session.query(SpareParts).filter(SpareParts.id == id_item).delete()
            session.commit()
            return JSONSuccess()
        finally:
            session.close()

    def get_xls(self):
        session = self.Session()
        try:
            splist = session.query(SpareParts)\
                            .all()
            workbook = Workbook()
            sheet = workbook.add_sheet(u'Данные')

            style_string = XFStyle()
            style_int = XFStyle()
            style_percent = XFStyle()

            style_string.num_format_str = "@"
            style_int.num_format_str = '0'
            style_percent.num_format_str = '0%'

            sheet.write(0, 0, u"code", style_string)
            sheet.write(0, 1, u"description", style_string)
            sheet.write(0, 2, u"description rus", style_string)
            sheet.write(0, 3, u"unit", style_string)
            sheet.write(0, 4, u"integer?", style_string)
            sheet.write(0, 5, u"safety stock", style_string)
            sheet.write(0, 6, u"planning type", style_string)
            sheet.write(0, 7, u"planning condition", style_string)
            sheet.write(0, 8, u"model", style_string)

            index = 0

            for row in splist:
                index += 1
                models = []
                for model in row.models:
                    models.append(model.descr)
                integer = 'Yes' if row.integer else 'No'
                planning_type = 'SYSTEM' if row.planning_type else 'MANUAL'
                sheet.write(index, 0, row.code, style_string)
                sheet.write(index, 1, row.description, style_string)
                sheet.write(index, 2, row.description_rus, style_string)
                sheet.write(index, 3, row.unit_desc, style_string)
                sheet.write(index, 4, integer, style_string)
                sheet.write(index, 5, row.safety_stock, style_int)
                sheet.write(index, 6, planning_type, style_string)
                sheet.write(index, 7, row.planning_condition / 100.0, style_percent)
                sheet.write(index, 8, ', '.join(models), style_string)

            unicname = os.path.basename(tempfile.mkstemp(prefix='SpareParts')[1])
            unicname += '.xls'
            workbook.save(os.path.join(TMPDIR, unicname))
            return "/tmp/" + unicname
        finally:
            session.close()


#    def get_models(self, query_filter=None):
#        qp = QueryProcess(self.__Session, Model, Model.descr, query_filter)
#        return qp.result()
#
#    def get_customer(self, query_filter=None):
#        qp = QueryProcess(self.__Session, Customer, Customer.descr, query_filter)
#        return qp.result()
#
#    def get_destination(self, query_filter=None):
#        qp = QueryProcess(self.__Session, Destination, Destination.descr, query_filter)
#        return qp.result()
#
#    def get_transaction_type(self, query_filter):
#        qp = QueryProcess(self.__Session, TransactionType, TransactionType.descr,
#            query_filter)
#        return qp.result()
#
#    def get_serial_number(self, query_filter, skip_repaired=False):
#        qp = QueryProcess(self.__Session, CoolerReceipt, CoolerReceipt.snumber,
#            query_filter)
#        query = qp.get_query()
#        if skip_repaired:
#            query = query.outerjoin(RepairNote)\
#                         .filter(RepairNote.dispatchdate == None)
#        return qp.result_by_query(query)
